package com.xuxueli.applyModules.utils;
import com.alibaba.druid.DbType;
import com.alibaba.druid.sql.SQLUtils;
import com.alibaba.druid.sql.ast.SQLExpr;
import com.alibaba.druid.sql.ast.SQLStatement;
import com.alibaba.druid.sql.ast.expr.SQLBinaryOpExpr;
import com.alibaba.druid.sql.ast.expr.SQLCharExpr;
import com.alibaba.druid.sql.ast.expr.SQLIdentifierExpr;
import com.alibaba.druid.sql.ast.expr.SQLIntegerExpr;
import com.alibaba.druid.sql.ast.statement.*;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlInsertStatement;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlSelectQueryBlock;
import com.alibaba.druid.sql.dialect.mysql.ast.statement.MySqlUpdateStatement;
import com.alibaba.druid.sql.dialect.mysql.parser.MySqlStatementParser;
import com.alibaba.druid.sql.dialect.mysql.visitor.MySqlSchemaStatVisitor;
import com.alibaba.druid.sql.dialect.sqlserver.ast.stmt.SQLServerInsertStatement;
import com.alibaba.druid.sql.parser.SQLStatementParser;
import com.alibaba.druid.sql.visitor.SchemaStatVisitor;
import com.alibaba.druid.stat.TableStat;
import com.xuxueli.applyModules.entity.QueryModelInfo;
import com.xuxueli.applyModules.entity.SysQueryMethodEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.core.io.support.PropertiesLoaderUtils;

import java.io.*;
import java.net.Socket;
import java.sql.*;
import java.util.*;
import java.util.concurrent.*;

/**
 * 说明：用于备份、还原数据库、在线编辑SQL
 *
 * 修改时间：2016年3月29日
 * @version
 */
@Slf4j
public class DbFHUtils {
    private static Log logger = LogFactory.getLog(DbFHUtils.class);
    private static Properties pros = getPprVue();
    public static Map<String, String> backUpTableList = new ConcurrentHashMap<String, String>();
    public static Map<String, String> recoverTableList = new ConcurrentHashMap<String, String>();
    private static DbFHUtils dbFH = new DbFHUtils();

    public static void main(String[] arg){
        try {
            //String str = DbFHUtils.getDbFH().backup("").toString();//调用数据库备份
            Object[] objects = new Object[0];
            objects = executeQueryFH("SELECT address_id, address, address2, district, city_id, postal_code, phone, location, last_update FROM sakila.address");
            System.out.println(objects);
            executeUpdateFH("update address set address = '47 MySakila Drives' where address_id = 1");
            Object[] tables = getTables();
            System.out.println(tables.length);
            QueryModelInfo sakila = parse("mysql", "SELECT address_id, address, address2, district, city_id, postal_code, phone, location, last_update FROM address where location like '%1%' and phone='21'");
            System.out.println(sakila);
            List<String> allColNameBySQL = getAllColNameBySQL("SELECT address_id, address, address2, district, city_id, postal_code, phone, location, last_update FROM address where location like '%1%' union");
            System.out.println(allColNameBySQL);
            parses("mysql","update test set status='P' where id=20");
            parses("mysql","insert into test (id,status,name,ce,acc) values (29,'P','lll','sxsx','Arferwg')");

        } catch (InterruptedException e) {
            e.printStackTrace();
        } catch (ExecutionException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public static DbFHUtils getDbFH(){
        return dbFH;
    }

    /**执行数据库备份入口
     * @param tableName 表名
     * @return
     * @throws InterruptedException
     * @throws ExecutionException
     */
    public Object backup(String tableName) throws InterruptedException, ExecutionException {
        if(null != backUpTableList.get(tableName)) return null;
        backUpTableList.put(tableName, tableName); 				// 标记已经用于备份(防止同时重复备份,比如备份一个表的线程正在运行，又发来一个备份此表的命令)
        ExecutorService pool = Executors.newFixedThreadPool(2);
        Callable<Object> fhc = new DbBackUpCallable(tableName);	//创建一个有返回值的线程
        Future<Object> f1 = pool.submit(fhc); 					//启动线程
        String backstr = f1.get().toString(); 					//获取线程执行完毕的返回值
        pool.shutdown();										//关闭线程
        return backstr;
    }

    /**执行数据库还原入口
     * @param tableName 表名
     * @param sqlFilePath 备份文件存放完整路径
     * @return
     * @throws InterruptedException
     * @throws ExecutionException
     */
    public Object recover(String tableName,String sqlFilePath) throws InterruptedException, ExecutionException {
        if(null != recoverTableList.get(tableName)) return null;
        recoverTableList.put(tableName, tableName); 							// 标记已经用于还原(防止同时重复还原,比如还原一个表的线程正在运行，又发来一个还原此表的命令)
        ExecutorService pool = Executors.newFixedThreadPool(2);
        Callable<Object> fhc = new DbRecoverCallable(tableName,sqlFilePath);	//创建一个有返回值的线程
        Future<Object> f1 = pool.submit(fhc); 									//启动线程
        String backstr = f1.get().toString(); 									//获取线程执行完毕的返回值
        pool.shutdown();														//关闭线程
        return backstr;
    }

    /**获取本数据库的所有表名
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Object[] getTables() throws ClassNotFoundException, SQLException{
        String dbtype = pros.getProperty("dbtype");				//数据库类型
        String username = pros.getProperty("username");			//用户名
        String password = pros.getProperty("password");			//密码
        String address = pros.getProperty("dbAddress");			//数据库连接地址
        String dbport = pros.getProperty("dbport");				//端口
        String databaseName = pros.getProperty("databaseName");	//数据库名
        Connection conn = DbFHUtils.getCon(dbtype,username,password,address+":"+dbport,databaseName);
        Object[] arrOb = {databaseName,DbFHUtils.getTablesByCon(conn, "sqlserver".equals(dbtype)?null:databaseName),dbtype};
        return arrOb;
    }

    /**
     * @return 获取conn对象
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    public static Connection getFHCon() throws ClassNotFoundException, SQLException{
        String dbtype = pros.getProperty("dbtype");				//数据库类型
        String username = pros.getProperty("username");			//用户名
        String password = pros.getProperty("password");			//密码
        String address = pros.getProperty("dbAddress");			//数据库连接地址
        String dbport = pros.getProperty("dbport");				//端口
        String databaseName = pros.getProperty("databaseName");	//数据库名
        return DbFHUtils.getCon(dbtype,username,password,address+":"+dbport,databaseName);
    }

    /**
     * @param dbtype	数据库类型
     * @param username	用户名
     * @param password	密码
     * @param dburl		数据库连接地址:端口
     * @param databaseName 数据库名
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Connection getCon(String dbtype,String username,String password,String dburl,String databaseName) throws SQLException, ClassNotFoundException{
        if("mysql".equals(dbtype)){
            Class.forName("com.mysql.cj.jdbc.Driver");
            return DriverManager.getConnection("jdbc:mysql://"+dburl+"/"+databaseName+"?user="+username+"&password="+password+"&serverTimezone=Asia/Shanghai&useSSL=false&characterEncoding=utf8");
        }else if("oracle".equals(dbtype)){
            Class.forName("oracle.jdbc.driver.OracleDriver");
            return DriverManager.getConnection("jdbc:oracle:thin:@"+dburl+":"+username, databaseName, password);
        }else if("sqlserver".equals(dbtype)){
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            return DriverManager.getConnection("jdbc:sqlserver://"+dburl+"; DatabaseName="+databaseName, username, password);
        }else{
            return null;
        }
    }

    /**获取某个conn下的所有表
     * @param conn 数据库连接对象
     * @param schemaName mysql:数据库名; oracle:用户名;sqlserver:null
     * @return
     */
    public static List<String> getTablesByCon(Connection conn, String schemaName) {
        try {
            List<String> listTb = new ArrayList<String>();
            DatabaseMetaData meta = conn.getMetaData();
            ResultSet rs = meta.getTables(conn.getCatalog(), schemaName, null, new String[] { "TABLE" });
            while (rs.next()) {
                listTb.add(rs.getString(3));
            }
            return listTb;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**用于执行某表的备份(内部类)线程
     * Callable 有返回值的线程接口
     */
    class DbBackUpCallable implements Callable<Object>{
        String tableName = null;
        public DbBackUpCallable(String tableName){
            this.tableName = tableName;
        }
        @Override
        public Object call() {
            try {
                String remoteDB = pros.getProperty("remoteDB");			//是否远程备份数据库 yes or no
                String DBSeverport = pros.getProperty("DBSeverport");	//远程服务器备份程序端口
                String dbtype = pros.getProperty("dbtype");				//数据库类型
                String username = pros.getProperty("username");			//用户名
                String password = pros.getProperty("password");			//密码
                String address = pros.getProperty("dbAddress");			//数据库连接地址
                String databaseName = pros.getProperty("databaseName");	//数据库名
                String dbpath = pros.getProperty("dbpath");			//数据库的安装路径
                String sqlpath = pros.getProperty("sqlFilePath");		//存储路径
                String ffilename = DateUtils.beforeDay();
                String commandStr = "";

                if(!"sqlserver".equals(dbtype)){
                    sqlpath = sqlpath+DateUtils.getDays()+"/";			//日期当路径分支
                    if("yes".equals(remoteDB)){//数据库另外一台服务器上(和tomcat不在同一台服务器上)
                        commandStr = DbFHUtils.getExecStr(dbtype,dbpath,"localhost",username,password,sqlpath,tableName,databaseName,ffilename); //命令语句
                        Socket ss = null;
                        DataOutputStream bb = null;
                        DataInputStream dat = null;
                        ss = new Socket(address, Integer.parseInt(DBSeverport));//连接远程服务器数据库备份程序
                        bb = new DataOutputStream(ss.getOutputStream());
                        dat = new DataInputStream(ss.getInputStream());
                        bb.writeUTF("FHQ313596790"+commandStr+"FH313596790"+sqlpath);	//发送指令给服务端
                        bb.flush();
                        Boolean llm = true;
                        while(llm){
                            String returnstr = dat.readUTF();
                            if("errer".equals(returnstr)){
                                return returnstr;	//远程服务器备份失败或超时
                            }
                            llm = false;
                            ss.close();
                            bb.close();
                            dat.close();
                        }
                    }else{							//数据库在本地(和tomcat在同一台服务器上)
                        FileUtil.createDir(sqlpath+"/fh.fh");
                        commandStr = DbFHUtils.getExecStr(dbtype,dbpath,address,username,password,sqlpath,tableName,databaseName,ffilename); //命令语句
                        Runtime cmd = Runtime.getRuntime();
                        Process p = cmd.exec(commandStr);
                        p.waitFor(); 				// 该语句用于标记，如果备份没有完成，则该线程持续等待
                    }
                }else{//当数据库为sqlserver时 只能备份整库，不能单表备份
                    String spath = sqlpath + databaseName + "_"+ffilename + ".bak";// name文件名
                    String bakSQL = "backup database "+databaseName+" to disk=? with init";// SQL语句
                    PreparedStatement bak = DbFHUtils.getFHCon().prepareStatement(bakSQL);
                    bak.setString(1, spath);// path必须是绝对路径
                    bak.execute(); 			// 备份数据库
                    bak.close();
                }
                String fileType=".bak";
                if("mysql".equals(dbtype)){
                    fileType=".sql";
                }else if("oracle".equals(dbtype)){
                    fileType=".DMP";
                }
                if("".equals(tableName)){
                    return sqlpath+databaseName+"_"+ffilename+fileType;
                }else{
                    return sqlpath+tableName+"_"+ffilename+fileType;
                }
            } catch (Exception e) {
                logger.error("备份操作出现问题", e);
                return "errer";
            }finally{
                backUpTableList.remove(tableName); // 最终都将解除
            }
        }
    }

    /**数据库备份命令字符串
     * @param dbtype 数据库类型
     * @param dbpath 数据库的路径
     * @param address 数据库连接地址
     * @param username 用户名
     * @param password 密码
     * @param sqlpath 存储路径
     * @param tableName 表名
     * @param databaseName 数据库名
     * @param ffilename 日期当路径和保存文件名的后半部分
     * @return 完整的命令字符串
     */
    public static String getExecStr(String dbtype,String dbpath,String address,String username,String password,String sqlpath,String tableName,String databaseName,String ffilename){
        StringBuffer sb = new StringBuffer();
        if("mysql".equals(dbtype)){
            address = "localhost";
            sb.append(dbpath);
            sb.append("mysqldump ");
            sb.append("--opt ");
            sb.append("-h ");
            sb.append(address);
            sb.append(" ");
            sb.append("--user=");
            sb.append(username);
            sb.append(" ");
            sb.append("--password=");
            sb.append(password);
            sb.append(" ");
            sb.append("--lock-all-tables=true ");
            sb.append("--result-file=");
            sb.append(sqlpath);
            sb.append(("".equals(tableName)?databaseName+"_"+ffilename:tableName+"_"+ffilename)+".sql");
            sb.append(" ");
            sb.append("--default-character-set=utf8 ");
            sb.append(databaseName);
            sb.append(" ");
            sb.append(tableName);//当tableName为“”时，备份整库
        }else if("oracle".equals(dbtype)){
            if("".equals(tableName)){//备份整库
                sb.append("EXP "+databaseName+"/"+password+" BUFFER=880000 FILE="+sqlpath+databaseName+"_"+ffilename+".DMP LOG="+sqlpath+databaseName+"_"+ffilename+".LOG OWNER="+databaseName);
            }else{//备份单表
                sb.append("EXP "+databaseName+"/"+password+" BUFFER=880000 FILE="+sqlpath+tableName+"_"+ffilename+".DMP LOG="+sqlpath+tableName+"_"+ffilename+".LOG TABLES=("+databaseName+"."+tableName+")");
            }
        }
        return sb.toString();
    }

    /**用于执行某表或整库的还原(内部类)线程
     * Callable 有返回值的线程接口
     */
    class DbRecoverCallable implements Callable<Object>{
        String tableName = null;
        String sqlFilePath = null;
        public DbRecoverCallable(String tableName,String sqlFilePath){
            this.tableName = tableName;
            this.sqlFilePath = sqlFilePath;
        }
        @Override
        public Object call() {
            try {
                String remoteDB = pros.getProperty("remoteDB");			//是否远程还原数据库 yes or no
                String DBSeverport = pros.getProperty("DBSeverport");	//远程服务器还原程序端口
                String dbtype = pros.getProperty("dbtype");				//数据库类型
                String username = pros.getProperty("username");			//用户名
                String password = pros.getProperty("password");			//密码
                String address = pros.getProperty("dbAddress");			//数据库连接地址
                String databaseName = pros.getProperty("databaseName");	//数据库名
                String dbpath = pros.getProperty("dbpath");				//数据库的安装路径
                if(!"sqlserver".equals(dbtype)){
                    if("yes".equals(remoteDB)){		//数据库另外一台服务器上(和tomcat不在同一台服务器上)
                        String commandStr="";
                        if("mysql".equals(dbtype)){
                            commandStr = "FHMysqlHyQ313596790"+dbpath+"mysql -u "+username+" -p"+password+" "+databaseName+"FH313596790"+sqlFilePath; //mysql还原命令语句
                        }else{
                            commandStr = "FHOracleHyQ313596790IMP "+databaseName+"/"+password+" FILE="+sqlFilePath+" LOG="+sqlFilePath.replace("DMP", "")+"LOG FULL=Y"; //oracle还原命令语句(还原前，先需要手动删除表,在sql编辑器里面删除即可)
                        }
                        Socket ss = null;
                        DataOutputStream bb = null;
                        DataInputStream dat = null;
                        ss = new Socket(address, Integer.parseInt(DBSeverport));//连接远程服务器数据库备份程序
                        bb = new DataOutputStream(ss.getOutputStream());
                        dat = new DataInputStream(ss.getInputStream());
                        bb.writeUTF(commandStr);	//发送指令给服务端
                        bb.flush();
                        Boolean llm = true;
                        while(llm){
                            String returnstr = dat.readUTF();
                            if("errer".equals(returnstr)){
                                return returnstr;	//远程服务器还原失败或超时
                            }
                            llm = false;
                            ss.close();
                            bb.close();
                            dat.close();
                        }
                        return "ok";
                    }else{							//数据库在本地(和tomcat在同一台服务器上)
                        if("mysql".equals(dbtype)){
                            this.recoverMysql(sqlFilePath, dbpath, username, password, databaseName);
                            return "ok";
                        }else{
                            String oracleCommandStr = "IMP "+databaseName+"/"+password+" FILE="+sqlFilePath+" LOG="+sqlFilePath.replace("DMP", "")+"LOG FULL=Y"; //oracle还原命令语句(还原前，先需要手动删除表,在sql编辑器里面删除即可
                            Runtime cmd = Runtime.getRuntime();
                            Process p = cmd.exec(oracleCommandStr);
                            p.waitFor();
                            return "ok";
                        }
                    }
                }else{//当数据库为sqlserver时
					/*String recoverySql = "ALTER DATABASE "+databaseName+" SET ONLINE WITH ROLLBACK IMMEDIATE";// 恢复所有连接
					PreparedStatement ps = DbFH.getFHCon().prepareStatement(recoverySql);
			        CallableStatement cs = DbFH.getFHCon().prepareCall("{call killrestore(?,?)}");
			        cs.setString(1, databaseName);	 // 数据库名
	                cs.setString(2, sqlFilePath); 	// 已备份数据库所在路径
	                cs.execute(); 					// 还原数据库
	                ps.execute(); 					// 恢复数据库连接   */
                    String reSQL = "use master exec killspid '"+databaseName+"' restore database "+databaseName+" from disk=? with replace"; // 还原数据库
                    PreparedStatement recovery = DbFHUtils.getFHCon().prepareStatement(reSQL);
                    recovery.setString(1, sqlFilePath);
                    if (!recovery.execute()){
                        return "ok";
                    }else{
                        return "errer";
                    }
                }
            } catch (Exception e) {
                logger.error("还原操作出现问题", e);
                return "errer";
            }finally{
                recoverTableList.remove(tableName); // 最终都将解除
            }
        }

        /**还原mysql数据库命令
         * @param sqlFilePath 备份文件的完整路径
         * @param dbpath mysql安装路径
         * @param username 用户名 例如：root
         * @param password 用户密码
         * @param databaseName 数据库名
         * @throws IOException
         */
        public void recoverMysql(String sqlFilePath,String dbpath,String username,String password,String databaseName) throws IOException{
            Runtime runtime = Runtime.getRuntime();
            Process process = runtime.exec(dbpath+"mysql -u "+username+" -p"+password+" "+databaseName);
            OutputStream outputStream = process.getOutputStream();
            BufferedReader br = new BufferedReader(new InputStreamReader(new FileInputStream(sqlFilePath), "utf8"));
            String str = null;
            StringBuffer sb = new StringBuffer();
            while((str = br.readLine()) != null){
                sb.append(str+"\r\n");
            }
            str = sb.toString();
            OutputStreamWriter writer = new OutputStreamWriter(outputStream,"utf-8");
            writer.write(str);
            writer.flush();
            outputStream.close();
            br.close();
            writer.close();
        }

    }

    /**动态读取数据记录
     * @param sql 查询语句
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static Object[] executeQueryFH(String sql) throws Exception{
        List<String> columnList = new ArrayList<String>();				//存放字段名
        List<List<Object>> dataList = new ArrayList<List<Object>>();	//存放数据(从数据库读出来的一条条的数据)
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = null;
        conn = DbFHUtils.getFHCon();
        stmt = conn.createStatement();
        rs = stmt.executeQuery(sql);
        columnList = DbFHUtils.getFieldLsit(conn,sql);
        while(rs.next()){
            List<Object> onedataList = new ArrayList<Object>(); 		//存放每条记录里面每个字段的值
            for(int i =1;i<columnList.size()+1;i++){
                onedataList.add(rs.getObject(i));
            }
            dataList.add(onedataList);									//把每条记录放list中
        }
        Object[] arrOb = {columnList,dataList};
        conn.close();
        return arrOb;
    }

    /**执行 INSERT、UPDATE 或 DELETE
     * @param sql 语句
     * @return
     * @throws SQLException
     * @throws ClassNotFoundException
     */
    public static void executeUpdateFH(String sql) throws ClassNotFoundException, SQLException{
        Statement stmt = null;
        Connection conn = null;
        conn = DbFHUtils.getFHCon();
        stmt = conn.createStatement();
        stmt.executeUpdate(sql);
        conn.close();
    }

    /**字段名列表
     * @param conn
     * @param table
     * @return
     * @throws SQLException
     */
    public static List<String> getFieldLsit(Connection conn, String table) throws SQLException{
        PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(table);
        pstmt.execute();  									//这点特别要注意:如果是Oracle而对于mysql可以不用加.
        List<String> columnList = new ArrayList<String>();	//存放字段
        ResultSetMetaData rsmd = (ResultSetMetaData) pstmt.getMetaData();
        for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
            columnList.add(rsmd.getColumnName(i));			//把字段名放list里
        }
        return columnList;
    }

    /**读取dbfh.properties 配置文件
     * @return
     * @throws IOException
     */
    public static Properties getPprVue() {
        InputStream inputStream = DbFHUtils.class.getClassLoader().getResourceAsStream("dbfh.properties");
        Properties p = new Properties();
        Properties prop = new Properties();
        try {
            p.load(inputStream);
            prop = PropertiesLoaderUtils.loadAllProperties("dbfh.properties");
            inputStream.close();
        } catch (IOException e) {
            //读取配置文件出错
            e.printStackTrace();
        }
        return p;
    }

    /**获取备份数据库的参数
     * @return
     */
    public static Map<String,String> getDBParameter(){
        Map<String,String> fhmap = new HashMap<String,String>();
        fhmap.put("dbtype", pros.getProperty("dbtype"));	//数据库类型
        fhmap.put("remoteDB", pros.getProperty("remoteDB"));//是否远程备份数据库 yes or no
        return fhmap;
    }

    private static List<String> getAllTableNameBySQL(String sql) {
        SQLStatementParser parser = new MySqlStatementParser(sql);
        // 使用Parser解析生成AST，这里SQLStatement就是AST
        SQLStatement sqlStatement = parser.parseStatement();
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        sqlStatement.accept(visitor);
        Map<TableStat.Name, TableStat> tables = visitor.getTables();
        List<String> allTableName = new ArrayList<>();
        for (TableStat.Name t : tables.keySet()) {
            allTableName.add(t.getName());
        }
        return allTableName;
    }

    private static List<String> getAllColNameBySQL(String sql) {
        List<String> responseParamList = new ArrayList<>();
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLSelectStatement sqlSelectStatement = (SQLSelectStatement) parser.parseSelect();
        MySqlSchemaStatVisitor mySqlSchemaStatVisitor = new MySqlSchemaStatVisitor();
        sqlSelectStatement.accept(mySqlSchemaStatVisitor);
        SQLSelect select = sqlSelectStatement.getSelect();
        SQLSelectQueryBlock queryBlock = select.getQueryBlock();
        if (queryBlock != null) {
            List<SQLSelectItem> selectList = queryBlock.getSelectList();
            selectList.forEach(sqlSelectItem -> {
                responseParamList.add(sqlSelectItem.getExpr().toString());
            });
        }else {
            if (StringUtils.containsIgnoreCase(sql, "union")) {
                SQLUnionQuery unionQuery = (SQLUnionQuery) select.getFirstQueryBlock().getParent();
                List<SQLSelectQuery> relations = unionQuery.getRelations();
                relations.forEach(query -> {
                    ((MySqlSelectQueryBlock) query).getSelectList().forEach(
                            sqlSelectItem -> {
                                if (sqlSelectItem.getAlias() != null) {
                                    responseParamList.add(sqlSelectItem.getAlias().toString());
                                } else {
                                    responseParamList.add(sqlSelectItem.getExpr().toString());
                                }
                            }
                    );
                });
            } else {
                //在解析union语句中selectList是Null，必须从mySqlSchemaStatVisitor中取
                Collection<TableStat.Column> columns = mySqlSchemaStatVisitor.getColumns();
                columns.forEach(item -> {
                    if (item.isSelect()) {
                        responseParamList.add(item.getName());
                    }
                });
            }
        }
        return responseParamList;
    }


    /**
     * 解析select sql生成QueryModelInfo
     * @param dbTypeName  mysql, oracle...
     * @param selectSql
     * @return
     */
    public static QueryModelInfo parse(String dbTypeName, String selectSql) {
        QueryModelInfo queryModelInfo = new QueryModelInfo();
        DbType dbType = DbType.valueOf(dbTypeName.toLowerCase());
        List<SQLStatement> statementList = SQLUtils.parseStatements(selectSql, dbType);
        for (SQLStatement statement : statementList) {
            if (statement instanceof SQLSelectStatement) {
                SchemaStatVisitor visitor = new SchemaStatVisitor(dbType);
                statement.accept(visitor);

                //解析表名
                SQLSelectStatement selectStatement = (SQLSelectStatement) statement;
                SQLSelectQueryBlock queryBlock =  selectStatement.getSelect().getFirstQueryBlock();
                SQLTableSource from = queryBlock.getFrom();
                SQLExprTableSource sqlExprTableSource = (SQLExprTableSource) from;
                String tableName = sqlExprTableSource.getTableName();
                queryModelInfo.setTableName(tableName);

                //查询列
                Collection<TableStat.Column> columns = visitor.getColumns();
                List<String> columnList = new ArrayList<>();
                columns.stream().forEach(row -> {
                    if(row.isSelect()){
                        //保存select字段
                        columnList.add(row.getName());
                    }
                });
                queryModelInfo.setColumnList(columnList);

                //查询过滤条件
                List<TableStat.Condition> conditions = visitor.getConditions();
                Map<String, SysQueryMethodEnum> whereMap = new HashMap<>();
                conditions.stream().forEach(row -> {
                    String columnName = row.getColumn().getName();
                    String operator = row.getOperator();
                    if("=".equalsIgnoreCase(operator)){
                        whereMap.put(columnName, SysQueryMethodEnum.eq);
                    }else if("LIKE".equalsIgnoreCase(operator)){
                        whereMap.put(columnName, SysQueryMethodEnum.like);
                    }else{
                        log.warn("un support operator "+operator+"; row = "+row);
                    }
                });
                queryModelInfo.setWhereMap(whereMap);
                break;
            }
        }
        return queryModelInfo;
    }

    /**
     * 解析sql语句
     * @return
     */
    public static void parses(String dbTypeName, String statement) {
        // 第一个参数为SQL语句
        // 第二个参数为解析的数据库类型
        List<SQLStatement> statementList = SQLUtils.parseStatements(statement,dbTypeName);
        // 单语句解析，只有一条数据
        if (!statement.isEmpty()) {
            SQLStatement sqlStatement = statementList.get(0);
            // 插入语句解析
            if (sqlStatement instanceof SQLServerInsertStatement) {
                // 转换
                MySqlInsertStatement insertStatement = (MySqlInsertStatement) sqlStatement;
                // 获取列名
                List<SQLExpr> columns = insertStatement.getColumns();
                List<String> columnsName = new ArrayList<>(columns.size());
                for (SQLExpr column : columns) {
                    columnsName.add(((SQLIdentifierExpr) column).getName());
                }
                System.out.println(columnsName);
                // 获取值
                List<SQLInsertStatement.ValuesClause> valuesList = insertStatement.getValuesList();
                List<List<Object>> dataList = new ArrayList<>();
                for (SQLInsertStatement.ValuesClause valuesClause : valuesList) {
                    List<SQLExpr> values = valuesClause.getValues();
                    List<Object> data = new ArrayList<>(columnsName.size());
                    for (SQLExpr value : values) {
                        data.add(getValue(value));
                    }
                    dataList.add(data);
                }
                System.out.println(dataList);
                // 获取表名
                System.out.println(insertStatement.getTableName().getSimpleName());
            } else if (sqlStatement instanceof MySqlUpdateStatement) {
                // 更新语句解析
                MySqlUpdateStatement updateStatement = (MySqlUpdateStatement) sqlStatement;
                // 获取更新的值和内容
                List<SQLUpdateSetItem> items = updateStatement.getItems();
                Map<String, Object> updateMap = new HashMap<>(items.size());
                for (SQLUpdateSetItem item : items) {
                    updateMap.put(((SQLIdentifierExpr) item.getColumn()).getName(), getValue(item.getValue()));
                }
                System.out.println(updateMap);
                // 获取条件，条件比较复杂，需根据实际情况自行提取
                SQLBinaryOpExpr where = (SQLBinaryOpExpr) updateStatement.getWhere();
                System.out.println(where);
                // 获取表名
                System.out.println(updateStatement.getTableName().getSimpleName());
            }
        }
    }

    public static void parsess(String sql) {
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLStatement sqlStatement = parser.parseStatement();
        MySqlInsertStatement mySqlInsertStatement = (MySqlInsertStatement)sqlStatement;
        List<SQLExpr> columns = mySqlInsertStatement.getColumns();
        SQLInsertStatement.ValuesClause values = mySqlInsertStatement.getValues();
        List<SQLInsertStatement.ValuesClause> valuesList = mySqlInsertStatement.getValuesList();

    }



    private static Object getValue(SQLExpr value) {
        // TODO 判断更多的种类
        if (value instanceof SQLIntegerExpr) {
            // 值是数字
            return ((SQLIntegerExpr) value).getNumber();
        } else if (value instanceof SQLCharExpr) {
            // 值是字符串
            return ((SQLCharExpr) value).getText();
        }
        return null;
    }


    private static void parseOracle(String sql){
        MySqlStatementParser parser = new MySqlStatementParser(sql);
        SQLInsertStatement insertStatement = (SQLInsertStatement) parser.parseInsert();
        MySqlSchemaStatVisitor visitor = new MySqlSchemaStatVisitor();
        insertStatement.accept(visitor);
        Collection columns = visitor.getColumns();
//        for (TableStat.Column column : columns) {
//            System.out.println(column);
//        }
        SQLSelect select = insertStatement.getQuery();
        SQLSelectQueryBlock queryBlock  = select.getQueryBlock();
        List<SQLSelectItem> selectItems = queryBlock.getSelectList();
        for (SQLSelectItem sqlSelectItem : selectItems) {
            System.out.println(sqlSelectItem);
        }
    }

}